﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;

namespace Caixa.DB
{
	internal static class SQLHelper
	{
		#region " Fields "

		public const string SQL_IDENTITY = "SELECT @@IDENTITY";

		#endregion

		#region " Métodos "

		public static string CreateInsertCommand(string tableName, DbParameter[] fields)
		{
			StringBuilder cmdSql = new StringBuilder("");

			cmdSql.Append("INSERT INTO " + tableName + " (");

			foreach (DbParameter field in fields)
			{
				cmdSql.Append(field.ParameterName.Replace("@", "") + ", ");
			}

			if (cmdSql.Length > 2)
			{
				cmdSql = cmdSql.Remove(cmdSql.Length - 2, 2);
			}

			cmdSql.Append(") VALUES (");

			foreach (DbParameter field in fields)
			{
				if (!field.ParameterName.StartsWith("@"))
				{
					field.ParameterName = "@" + field.ParameterName;
				}
				cmdSql.Append(field.ParameterName + ", ");
			}

			if (cmdSql.Length > 2)
			{
				cmdSql = cmdSql.Remove(cmdSql.Length - 2, 2);
			}

			cmdSql.Append(");");

			//cmdSql.Append(" SELECT SCOPE_IDENTITY(); ");

			return cmdSql.ToString();
		}

		public static string CreateUpdateCommand(string tableName, DbParameter[] fields, string whereFilter)
		{
			StringBuilder cmdSql = new StringBuilder("");

			cmdSql.Append("UPDATE " + tableName + " SET ");

			foreach (DbParameter field in fields)
			{
				cmdSql.Append(field.ParameterName.Replace("@", "") + " = ");
				if (!field.ParameterName.StartsWith("@"))
				{
					field.ParameterName = "@" + field.ParameterName;
				}
				cmdSql.Append(field.ParameterName + ", ");
			}

			if (cmdSql.Length > 2)
			{
				cmdSql = cmdSql.Remove(cmdSql.Length - 2, 2);
			}

			if (whereFilter.Trim() != String.Empty)
			{
				cmdSql.Append(" WHERE ");
				cmdSql.Append("(" + whereFilter + ")");
			}

			return cmdSql.ToString();
		}

		public static string CreateDeleteCommand(string tableName, string whereFilter)
		{
			StringBuilder cmdSql = new StringBuilder("");

			cmdSql.Append("DELETE FROM " + tableName + " ");
			if (whereFilter.Trim() != String.Empty)
			{
				cmdSql.Append(" WHERE ");
				cmdSql.Append("(" + whereFilter + ")");
			}

			return cmdSql.ToString();
		}

		#endregion
	}
}
